![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
By applying referential integrity constraints to the DOGS and BREEDS tables, you can guarantee that the DOGS table does not reference any value for a dogs breed that does not exist. This constraint is applied by first adding BREED as a primary key in the BREEDS table: SQL> ALTER TABLE breeds 2 ADD PRIMARY KEY (breed); Table altered. Then you add BREED as a foreign key in the DOGS table: SQL> ALTER TABLE dogs 2 ADD FOREIGN KEY (breed) REFERENCES breeds(breed); Table altered. After you add these constraints, any attempt to add an additional entry to the DOGS table that references a nonexistent value in the BREEDS table results in the following error: SQL> INSERT INTO dogs 2 ( dogname, age, breed, owner ) 3 VALUES 4 ( 'Lacy', 5, 12, 'Letterman' ); INSERT INTO dogs ERROR at line 1: ORA-02291: integrity constraint (ED.SYS_C00392) violated - parent key not found By correcting the error, the INSERT operation will succeed: SQL> INSERT INTO dogs 2 ( dogname, age, breed, owner ) 3 VALUES 4 ( 'Lacy', 5, 2, 'Letterman' ); 1 row created. By using referential integrity constraints, you reduce the chance of errors and ensure the integrity of your data. Some types of data checking (such as verifying that a field is numeric or verifying that all fields are entered) may be better done in the application code; however, in most other cases, checking is handled more efficiently by Oracle. A good rule of thumb is that if you have to issue an SQL statement to verify referential integrity, you can better protect your data with a referential integrity constraint. Integrity ConstraintsBy using the features of the database, you can reduce processing at the application or the database level. Some integrity constraints can verify that your data conforms to requirements you set up in your tables. Following is a list of a few of these constraints:
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |